library(rgdal)
library(ggplot2)
library(scales)
library(maptools)
library(gpclib)
library(gridExtra)
require(grid)

# Functions
sqLiteConnect <- function(database, table) {
  require(DBI)
  con <- dbConnect(RSQLite::SQLite(), dbname = database)
  query <- dbSendQuery(con, paste("SELECT * FROM ", table, ";", sep="")) 
  result <- fetch(query, n = -1)
  dbClearResult(query)
  dbDisconnect(con)
  return(result)
}

# Load meetup tables
meetup_user <-  sqLiteConnect("m5s_meetup_jun2015.sqlite", "member")
meetup_group <- sqLiteConnect("m5s_meetup_jun2015.sqlite", "[group]")
meetup_event <- sqLiteConnect("m5s_meetup_jun2015.sqlite", "event")
meetup_group_member <- sqLiteConnect("m5s_meetup_jun2015.sqlite", "group_member")

# Growth analysis
min_date <- as.Date(min(meetup_group_member$joined))
date_vday_07 <- as.Date('2007-09-08')
date_admin_elections_12 <- as.Date('2012-05-06')
date_sicily_election_12 <- as.Date('2012-10-28')
date_gener_election_13 <- as.Date('2013-02-24')
date_europ_election_14 <- as.Date('2014-05-22')

require(dplyr)
mmbs_firtst_join <-
  meetup_group_member %>%
  dplyr::group_by(member_id) %>%
  dplyr::summarise(first_date = as.Date(min(joined)))

lst <- list(c(min_date, date_vday_07),
            c(date_vday_07, date_admin_elections_12),
            c(date_admin_elections_12, date_gener_election_13),
            c(date_gener_election_13 - 30, date_gener_election_13),
            c(date_gener_election_13, date_gener_election_13 + 30),
            c(date_gener_election_13, date_europ_election_14))

# for (d in lst) {
#   cat(paste0("From: ", as.character(d[1]), " to: " , as.character(d[2])))
#   cat("\n")
#   cat(nrow(subset(mmbs_firtst_join, first_date >= d[1] & first_date < d[2])) /
#     as.numeric(d[2] - d[1]))
#   cat("\n\n")
# }

# Time sequence
wk_seq <- seq(min(as.Date(meetup_group$created)), max(as.Date(meetup_group$created)), by=7) 
meetup_group_weekly <- as.data.frame(table(cut(as.Date(meetup_group$created), breaks = wk_seq)))
meetup_user_weekly <- as.data.frame(table(cut(as.Date(meetup_user$joined), breaks = wk_seq)))
meetup_event_weekly <- as.data.frame(table(cut(as.Date(meetup_event$time), breaks = wk_seq)))

# Snapshot dates
date1 = as.numeric(as.Date("2006-01-01"))
date2 = as.numeric(as.Date("2008-01-01"))
date3 = as.numeric(as.Date("2010-01-01"))
date4 = as.numeric(as.Date("2012-01-01"))
date5 = as.numeric(as.Date("2014-01-01"))

# First national assembly
# line1 = as.numeric(as.Date("2009-03-08"))
# First regional election
# line2 = as.numeric(as.Date("2010-03-29"))
# Local elections
# line3 = as.numeric(as.Date("2011-05-16"))
# Local elections
line4 = as.numeric(as.Date("2012-05-07"))
# Sicilian elections
# line5 = as.numeric(as.Date("2012-10-28"))
# National elections
line6 = as.numeric(as.Date("2013-02-25"))
# V-Day
line7 = as.numeric(as.Date("2007-09-08"))
# European election
line8 = as.numeric(as.Date("2014-05-22"))

# Analysis to be embedded
m5s_meetup_stat <- list()
m5s_meetup_stat$group$weekly <- meetup_group_weekly
m5s_meetup_stat$group$weekly$cumsum <- cumsum(meetup_group_weekly$Freq)
m5s_meetup_stat$user$weekly <- meetup_user_weekly
m5s_meetup_stat$user$weekly$cumsum <- cumsum(meetup_user_weekly$Freq)
m5s_meetup_stat$event$weekly <- meetup_event_weekly
m5s_meetup_stat$event$weekly$cumsum <- cumsum(meetup_event_weekly$Freq)

# Plots
meetup_ts_group <- ggplot(meetup_group_weekly , aes(x=as.Date(Var1), y=cumsum(Freq))) + geom_line() + geom_area(fill="gray") +
  labs(x=NULL, y="Groups") +
  theme(axis.text.x=element_blank(),
        axis.title.x=element_blank(),
        plot.title=element_blank(),
        axis.ticks.x=element_blank(),
        plot.margin = unit(c(0.5,0.5,0,0.5), "lines"),
        panel.background = element_blank(),
        panel.grid.major = element_blank(),
        panel.grid.minor = element_blank(),
        panel.border = element_blank(),
        axis.line = element_line(colour = "gray")) +
  # geom_vline(xintercept=line1, linetype="dotted", size=.2) +
  # geom_vline(xintercept=line2, linetype="dotted", size=.2) +
  # geom_vline(xintercept=line3, linetype="dotted", size=.2) +
  geom_vline(xintercept=line4, linetype="dotted", size=.2) +
  # geom_vline(xintercept=line5, linetype="dotted", size=.2) +
  geom_vline(xintercept=line6, linetype="dotted", size=.2) +
  geom_vline(xintercept=line7, linetype="dotted", size=.2) +
  geom_vline(xintercept=line8, linetype="dotted", size=.2) +
  annotate("text", x=as.Date(line6, origin = "1970-01-01"), y=1300, 
           label="General\nelection", size=3, family="Palatino") +
  annotate("text", x=as.Date(line8, origin = "1970-01-01"), y=1500, 
           label="European\nelection", size=3, family="Palatino")

meetup_ts_user <- ggplot(meetup_user_weekly , aes(x=as.Date(Var1), y=cumsum(Freq))) + geom_line() + geom_area(fill="gray") +
  labs(x=NULL, y="Members ") + scale_y_continuous(labels=comma) +
  theme(axis.text.x=element_blank(),
        axis.title.x=element_blank(),
        plot.title=element_blank(),
        axis.ticks.x=element_blank(),
        plot.margin = unit(c(0,0.5,0,0.5), "lines"),
        panel.background = element_blank(),
        panel.grid.major = element_blank(),
        panel.grid.minor = element_blank(),
        panel.border = element_blank(),
        axis.line = element_line(colour = "gray")) +
  # geom_vline(xintercept=line1, linetype="dotted", size=.2) +
  # geom_vline(xintercept=line2, linetype="dotted", size=.2) +
  # geom_vline(xintercept=line3, linetype="dotted", size=.2) +
  geom_vline(xintercept=line4, linetype="dotted", size=.2) +
  # geom_vline(xintercept=line5, linetype="dotted", size=.2) +
  geom_vline(xintercept=line6, linetype="dotted", size=.2) +
  geom_vline(xintercept=line7, linetype="dotted", size=.2) +
  geom_vline(xintercept=line8, linetype="dotted", size=.2) +
  annotate("text", x=as.Date(line4, origin = "1970-01-01"), y=75000, 
           label="Local\nelections", size=3, family="Palatino") +
  annotate("text", x=as.Date(line7, origin = "1970-01-01"), y=50000, 
           label="First\nV-Day", size=3, family="Palatino")

meetup_ts_event <- ggplot(meetup_event_weekly , aes(x=as.Date(Var1), y=Freq)) + geom_line() + geom_area(fill="gray") +
  labs(x=NULL, y="Events") +
  theme(plot.title=element_blank(),
        plot.margin = unit(c(0,0.5,0.5,0.5), "lines"),
        panel.background = element_blank(),
        panel.grid.major = element_blank(),
        panel.grid.minor = element_blank(),
        panel.border = element_blank(),
        axis.line = element_line(colour = "gray")) +
  # geom_vline(xintercept=line1, linetype="dotted", size=.2) +
  # geom_vline(xintercept=line2, linetype="dotted", size=.2) +
  # geom_vline(xintercept=line3, linetype="dotted", size=.2) +
  geom_vline(xintercept=line4, linetype="dotted", size=.2) +
  # geom_vline(xintercept=line5, linetype="dotted", size=.2) +
  geom_vline(xintercept=line6, linetype="dotted", size=.2) +
  geom_vline(xintercept=line7, linetype="dotted", size=.2) +
  geom_vline(xintercept=line8, linetype="dotted", size=.2)
#   annotate("text", x=as.Date(line1, origin = "1970-01-01"), y=2000, 
#            label="National\nassembly", 
#            family= theme_get()$text[["family"]], 
#            size=theme_get()$text[["size"]]/2.5, 
#            fontface="italic") +
#   annotate("text", x=as.Date(line2, origin = "1970-01-01"), y=2000, 
#            label="Regional\nelections",
#            family= theme_get()$text[["family"]], 
#            size=theme_get()$text[["size"]]/2.5, 
#            fontface="italic") +
#   annotate("text", x=as.Date(line3, origin = "1970-01-01"), y=2000, 
#            label="Local\nelections", 
#            family= theme_get()$text[["family"]], 
#            size=theme_get()$text[["size"]]/2.5, 
#            fontface="italic") +
#   annotate("text", x=as.Date(line5, origin = "1970-01-01"), y=2000, 
#            label="Sicilian\nelections",
#            family= theme_get()$text[["family"]], 
#            # size=theme_get()$text[["size"]]/2.5, 
#            fontface="italic")

gp1<- ggplot_gtable(ggplot_build(meetup_ts_group))
gp2<- ggplot_gtable(ggplot_build(meetup_ts_user))
gp3<- ggplot_gtable(ggplot_build(meetup_ts_event))
maxWidth = unit.pmax(gp1$widths[2:3], gp2$widths[2:3],gp3$widths[2:3])
gp1$widths[2:3] <- maxWidth
gp2$widths[2:3] <- maxWidth
gp3$widths[2:3] <- maxWidth

grid.arrange(gp1,gp2,gp3)
